import sys
NeededPaths=['/opt/coopip/djangoproject/include', '/opt/coopip/djangoproject', '/opt/cis', '/opt/oms/', '/opt/amr', '/opt/gis','/opt/gos', '/opt']
for path in NeededPaths:
    if(not path in sys.path):
        sys.path.append(path)
import httplib
import os, csv, shutil, datetime, time, reportlab, djangoproject.settings, datetime
from django.contrib.gis.db import models
from django.contrib.gis.geos import *
from django.http import HttpResponse
from django.contrib.gis import admin
import _mysql as mysql
from django.db import transaction
import django




import CIS, AMR, GIS, GOS  #Comment this out before doing a syncdb





class IncidentUsername(models.Model):
    using='default'
    Incident=models.ForeignKey('Incident', db_column='incident')
    Username=models.TextField(db_column='username', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'incidentusername'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.Incident) + ' ' + str(self.Username)


def TwoDigitString(Integer):
    Temp = str(Integer)
    if(len(Temp) == 1):
        Temp = '0' + Temp
    return Temp

class RevenueProtectionReport(models.Model):
    using='default'
    FileName=models.TextField(db_column='filename')
    RunReportStartDateTime=models.DateTimeField(db_column='runreportstartdatetime', null=True, blank=True)
    RunReportCompleteDateTime=models.DateTimeField(db_column='runreportcompletedatetime', null=True, blank=True)
    FileSize=models.BigIntegerField(db_column='filesize')
    Username=models.TextField(db_column='username', null=True, blank=True)  #User that Ran the Report, "Nightly" if the report was an automatic nightly report    
    objects=models.GeoManager()
    class Meta:
        db_table=u'revenueprotectionreport'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.Incident) + ' ' + str(self.Username)
    @staticmethod
    def GenerateReport(Username='Nightly'):
        New=RevenueProtectionReport()
        New.Username=Username
        Now=datetime.datetime.now()
        day = Now.day
        
        New.FileName=djangoproject.settings.RevenueProtectionNightlyPDFReportLocation + str(Now.year) + '-' + TwoDigitString(Now.month) + '-' + TwoDigitString(Now.day) + '__' + TwoDigitString(Now.hour) + ':' + \
                                                                                        TwoDigitString(Now.minute) + ':' + TwoDigitString(Now.second) + '.pdf'
        New.RunReportStartDateTime=Now
        New.FileSize=0
        New.save()
        COOPIP_ReportLabRevenueProtectionReport.CreateRevenueProtectionReportPDF(New)
        New.RunReportCompleteDateTime=datetime.datetime.now()
        try: 
            New.FileSize=os.path.getsize(New.FileName)
        except:
            pass 
        New.save()
    def DeleteReport(self):
        if(self.Username == 'Nightly'):
            return
        if(os.path.exists(self.FileName)):
            os.remove(self.FileName)
        self.delete()
    def GetFileNameWithoutPath(self):
        Temp = self.FileName[:]
        while('/' in Temp):
            try:
                Temp = Temp[Temp.find('/') + 1:]
            except:
                return ''
        return Temp


class Incident(models.Model):
    using='default'
    MemberSeparator=models.TextField(db_column='memberseparator', null=True, blank=True)
    InitialFindingsIndicator=models.NullBooleanField(db_column='initialfindingsindicator', null=True, blank=True)
    InitialFindingsDateTime=models.DateTimeField(db_column='initialfindingsdatetime', null=True, blank=True)
    InitialFindingsDescription=models.TextField(db_column='initialfindingsdescription', null=True, blank=True)
    InProgressIndicator=models.NullBooleanField(db_column='inprogressindicator', null=True, blank=True)
    InProgressDateTime=models.DateTimeField(db_column='inprogressdatetime', null=True, blank=True)
    InProgressDescription=models.TextField(db_column='inprogressdescription', null=True, blank=True)
    FilmIndicator=models.NullBooleanField(db_column='filmindicator', null=True, blank=True)
    FilmUploadedIndicator=models.NullBooleanField(db_column='filmuploadedindicator', null=True, blank=True)
    ClosedIndicator=models.NullBooleanField(db_column='closedindicator', null=True, blank=True)
    ClosedDateTime=models.DateTimeField(db_column='closeddatetime', null=True, blank=True)
    ClosedDescription=models.TextField(db_column='closedescription', null=True, blank=True)
    CutSealIndicator=models.NullBooleanField(db_column='cutsealindicator', null=True, blank=True)
    MeterProperPositionIndicator=models.NullBooleanField(db_column='meterproperpositionindicator', null=True, blank=True)
    MeterConditionDescription=models.TextField(db_column='meterconditiondescription', null=True, blank=True)
    MeterSealInPlaceIndicator=models.NullBooleanField(db_column='metersealinplaceindicator', null=True, blank=True)
    JumperIndicator=models.NullBooleanField(db_column='jumperindicator', null=True, blank=True)
    MeterBaseGoodConditionIndicator=models.NullBooleanField(db_column='meterbasegoodconditionindicator', null=True, blank=True)
    LockGoodConditionIndicator=models.NullBooleanField(db_column='lockgoodconditionindicator', null=True, blank=True)
    MapNumber=models.TextField(db_column='mapnumber', null=True, blank=True)
    VehicleTagNumber=models.TextField(db_column='tagnumber', null=True, blank=True)
    VehicleDescription=models.TextField(db_column='vehicledescription', null=True, blank=True)
    CutSealCharge=models.DecimalField(db_column='cutsealcharge', null=True, blank=True, max_digits=12, decimal_places=2)
    TamperCharge=models.DecimalField(db_column='tampercharge', null=True, blank=True, max_digits=12, decimal_places=2)
    ServiceCharge=models.DecimalField(db_column='servicecharge', null=True, blank=True, max_digits=12, decimal_places=2)
    VehiclesAtLocation=models.TextField(db_column='vehiclesatlocation', null=True, blank=True)
    MiscellaneousCharge=models.DecimalField(db_column='miscellaneouscharge', null=True, blank=True, max_digits=12, decimal_places=2)
    ChargeDescription=models.TextField(db_column='chargedescription', null=True, blank=True)
    StolenCurrentCharge=models.DecimalField(db_column='stolencurrentcharge', null=True, blank=True, max_digits=12, decimal_places=2)
    TotalTheftCharges=models.DecimalField(db_column='totaltheftcharges', null=True, blank=True, max_digits=12, decimal_places=2)
    CaseNumber=models.TextField(db_column='casenumber', null=True, blank=True)
    County=models.ForeignKey('County', db_column='county', null=True, blank=True)
    WarrantRequestedIndicator=models.NullBooleanField(db_column='warrantrequestedindicator', null=True, blank=True)
    PastDueBillAmount=models.DecimalField(db_column='pastduebillamount', null=True, blank=True, max_digits=12, decimal_places=2)
    BadDebtAmount=models.DecimalField(db_column='baddebtamount', null=True, blank=True, max_digits=12, decimal_places=2)
    HearingDateTime=models.DateField(db_column='hearingdatetime', null=True, blank=True)
    Decision=models.TextField(db_column='decision', null=True, blank=True)
    OrderRestitutionIndicator=models.NullBooleanField('orderrestitutionindicator', null=True, blank=True)
    Point=models.PointField(db_column='the_geom', srid=djangoproject.settings.COOPIPGPSSRID, null=True, blank=True)
    AccountName=models.TextField(db_column='accountname', null=True, blank=True)  #Name on the account
    AddressOne=models.TextField(db_column='addressone', null=True, blank=True)
    AddressTwo=models.TextField(db_column='addresstwo', null=True, blank=True)
    AddressThree=models.TextField(db_column='addressthree', null=True, blank=True)
    City=models.TextField(db_column='city', null=True, blank=True)
    State=models.TextField(db_column='state', null=True, blank=True)
    Zip=models.TextField(db_column='zip', null=True, blank=True)
    HomePhone=models.TextField(db_column='homephone', null=True, blank=True)
    CellPhone=models.TextField(db_column='cellphone', null=True, blank=True)
    OtherPhone=models.TextField(db_column='otherphone', null=True, blank=True)
    Fax=models.TextField(db_column='fax', null=True, blank=True)
    Email=models.TextField(db_column='email', null=True, blank=True)
    EvidenceBagNumber=models.TextField(db_column='evidencebagnumber', null=True, blank=True)
    LegalCodeTheftIndicator=models.NullBooleanField(db_column='legalcodetheftindicator', null=True, blank=True)
    LegalCodeAlterIndicator=models.NullBooleanField(db_column='legalcodealterindicator', null=True, blank=True)
    LegalCodeCheatIndicator=models.NullBooleanField(db_column='legalcodecheatindicator', null=True, blank=True)
    Description=models.TextField(db_column='description', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'incident'
        app_label='djangoproject'
    def __unicode__(self):
        StringList=[]
        StringList.append(str(self.id) + ":  ")
        if(not self.Point):
            StringList.append('*Location Needed*  ')
        StringList.append(str(self.InitialFindingsDateTime)[:10].ljust(11))
        if(self.ClosedIndicator):
            StringList.append('Closed  ')
        elif(self.InProgressIndicator):
            StringList.append('In Progress  ')
        else:
            StringList.append('Initial Findings ')
        StringList.append(str(self.AccountName))
        StringList.append(str(self.Description))

        return ''.join(StringList)


class IncidentNote(models.Model):
    using='default'
    Incident=models.ForeignKey('Incident', db_column='incident')
    Description=models.TextField(db_column='description', null=True, blank=True)
    Username=models.TextField(db_column='username', null=True, blank=True)
    DateTime=models.DateTimeField(db_column='datetime', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table='incidentnote'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.DateTime)[:10].ljust(12) + str(self.Username).ljust(20) + str(self.Description)



class IncidentFile(models.Model):
    using='default'
    FileName=models.TextField(db_column='filename', null=True, blank=True)
    Incident=models.ForeignKey('Incident', db_column='incident', null=True, blank=True)
    File=models.FileField(max_length=250, upload_to='incident', null=True, blank=True)
    DateTime=models.DateTimeField(db_column='datetime', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'incidentfile'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.Incident) + ' ' + str(self.FileName)


class IncidentPayment(models.Model):
    using='default'
    Incident=models.ForeignKey('Incident', db_column='incident')
    Description=models.TextField(db_column='description', null=True, blank=True)
    Username=models.TextField(db_column='username', null=True, blank=True)
    DateTime=models.DateTimeField(db_column='datetime', null=True, blank=True)
    Amount=models.DecimalField(db_column='amount', null=True, blank=True, max_digits=12, decimal_places=2)
    objects=models.GeoManager()
    class Meta:
        db_table='incidentpayment'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.DateTime)[:10].ljust(12) + str(self.Username).ljust(20) + str(self.Description)


class County(models.Model):
    using='default'
    Name=models.TextField(db_column='Name', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'county'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.Name)

class MeterReadingAnalysis(models.Model):
    using='default'
    MeterSerialNumber=models.IntegerField(db_column='meterserialnumber', null=True, blank=True)
    AnalysisDateTime=models.DateTimeField(db_column='analysisdatetime', null=True, blank=True)
    CreateDateTime=models.DateTimeField(db_column='createdatetime', null=True, blank=True)
    UsagePastDayKWH=models.IntegerField(db_column='usagepastdaykwh', null=True, blank=True)
    UsagePastWeekKWH=models.IntegerField(db_column='usagepastweekkwh', null=True, blank=True)
    UsagePastMonthKWH=models.IntegerField(db_column='usagepastmonthkwh', null=True, blank=True)
    UsagePastYearKWH=models.IntegerField(db_column='usagepastyearkwh', null=True, blank=True)
    class Meta:
        db_table=u'meterreadinganalysis'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.MeterSerialNumber) +':'+ self.AnalysisDateTime.strftime('%m/%d/%Y %H:%M')
    def CalculateMeterUsageSinceDateTime(self, DateTime):
        DateTime=datetime.datetime(year=DateTime.year, month=DateTime.month, day=DateTime.day) #inclusive of events earlier that day
        RawDataToCalculate = self.Cache.GetRawDataSinceDateTime()#list returns from cache

    def PerformAnalysis(self, Meter, DateTime, Cache):
        self.Cache=Cache
        MeterReadingAnalysis=djangoproject.models.revenueprotection.MeterReadingAnalysis()
        MeterReadingAnalysis.MeterSerialNumber=Meter.SerialNumber
        MeterReadingAnalysis.CreateDateTime=datetime.datetime.now()

        PastDayKWH = self.CalculateUsageSinceDateTime(DateTime - datetime.timedelta(days=1))
        PastWeekKWH = self.CalculateUsageSinceDateTime(DateTime - datetime.timedelta(days=7))
        PastMonthKWH = self.CalculateUsageSinceDateTime(DateTime - datetime.timedelta(days=30))
        PastYearKWH = self.CalculateUsageSinceDateTime(DateTime - datetime.timedelta(days=365))

        MeterReadingAnalysis.UsagePastDayKWH = PastDayKWH
        MeterReadingAnalysis.save()


def GetNearbySpans(station):
    Spans1=GIS.Coopgisspan.objects.filter(startstation=station)
    Spans2=GIS.Coopgisspan.objects.filter(endstation=station)
    SpanList=[]
    for span in Spans1:
        SpanList.append(span)
    for span in Spans2:
        SpanList.append(span)
    return SpanList


def FarStation(span, CloseStation):
    if(span.startstation == CloseStation):
        return span.endstation
    if(span.endstation == CloseStation):
        return span.startstation
    return None

class TransformerUsageAnalysis(models.Model):
    """TransformerUsageAnalysis Stores the results of transformer analysis, performed on a daily basis."""
    using='default'
    TransformerAssemblyIdentifier=models.IntegerField(db_column='transformerassemblyidentifier', blank=True, null=True)
    CircuitNumber=models.IntegerField(db_column='circuitnumber', blank=True, null=True)
    StationIdentifier=models.IntegerField(db_column='stationidentifier', blank=True, null=True)
    Point=models.PointField(db_column='the_geom', srid=djangoproject.settings.COOPIPSRID)
    AnalysisDateTime=models.DateTimeField(db_column='analysisdatetime',null=True, blank=True)
    CreateDateTime=models.DateTimeField(db_column='createdatetime',null=True, blank=True)
    MaxUtilizationPercent=models.FloatField(db_column='maxutilizationpercent', null=True, blank=True)
    AverageUtilizationPercent=models.FloatField(db_column='averageutilizationpercent', null=True, blank=True)
    TotalMeterUsage=models.FloatField(db_column='totalmeterusage', null=True, blank=True)
    TotalLightUsage=models.FloatField(db_column='totallightusage', null=True, blank=True)
    MaxUsage=models.FloatField(db_column='maxusage', null=True, blank=True)
    MeterNumberList=models.TextField(db_column='meternumberlist', null=True, blank=True)
    LightCompatibleUnitList=models.TextField(db_column='lightcompatibleunitlist', null=True, blank=True)
    MultipleTransformersExistAtStationIndicator=models.NullBooleanField(db_column='multipletransformersexistatstationindicator', null=True, blank=True)
    MissingReadingIndicator=models.NullBooleanField(db_column='missingreadingindicator', null=True, blank=True)
    TransformerCompatibleUnitNameList=models.TextField(db_column='transformercompatibleunitnamelist', null=True, blank=True)
    objects=models.GeoManager()
    class Meta:
        db_table=u'transformerusageanalysis'
        app_label='djangoproject'
    def __unicode__(self):
        return str(self.id)
    def GetConnectedNearbySpans(self, station):
        NearbySpans=GetNearbySpans(station)
        ConnectedSpans=[]
        InstallStatus_id_Install=GIS.Coopgisinstallstatus.objects.filter(name='Install')[0].id
        for span in NearbySpans:  #No Phase or Circuit Enforcement TODO
            if(GIS.Coopgisassembly.objects.filter(span=span).filter(secondaryindicator=True).filter(compatibleunit__conductorindicator=True).exclude(installstatus_id=InstallStatus_id_Install) | 
               GIS.Coopgisassembly.objects.filter(span=span).filter(serviceindicator=True).filter(compatibleunit__conductorindicator=True).exclude(installstatus_id=InstallStatus_id_Install)):
                if(not span in ConnectedSpans):
                    ConnectedSpans.append(span)
        return ConnectedSpans
    def GetConnectedStations(self, Transformer):
        StationsToVisit=[Transformer.station]
        ConnectedStations=[]
        SpansVisited=[]
        while StationsToVisit:
            CurrentStation=StationsToVisit.pop()
            ConnectedSpans=self.GetConnectedNearbySpans(CurrentStation)
            if(not CurrentStation in ConnectedStations):
                ConnectedStations.append(CurrentStation)            
            for span in ConnectedSpans:
                if(not FarStation(span, CurrentStation) in ConnectedStations):
                    StationsToVisit.append(FarStation(span, CurrentStation))
        return ConnectedStations
    def GetMetersOffOfTransformer(self, Transformer):
        Stations=self.GetConnectedStations(Transformer)
        Meters=[]
        for station in Stations:  
            meters=GIS.Coopgisassembly.objects.filter(station=station).filter(compatibleunit__meterindicator=True)
            for meter in meters:
                Meters.append(meter)
        return Meters

    def GetLightsOffOfTransformer(self, Transformer):
        Stations=self.GetConnectedStations(Transformer)
        Lights=[]
        for station in Stations:  
            lights=GIS.Coopgisassembly.objects.filter(station=station).filter(compatibleunit__lightindicator=True)
            for light in lights:
                Lights.append(light)
        return Lights
  
    def GetMeterUsage(self, Meter, DateTime):  
        """GetMeterUsage returns a list that contains the usage for a meter with 24 elements, 1 for each hour of the day.  It attempts to estimate any missing 
           readings.  If it canot estimate, it returns a list with a single element:  ['MissingData']"""
        Results=[]
        try:
            serialnumber=int(Meter.meterdcsinumber)
        except:
            return ['MissingData']
        DateTime=datetime.datetime(year=DateTime.year, month=DateTime.month, day=DateTime.day)
        meterRead=djangoproject.models.ipbase.MeterRead.objects.filter(Meter__SerialNumber=serialnumber)
        if(not meterRead):
            return ['MissingData']
        meterReadHourlyReadingNetDict=meterRead[0].GetHourlyReadingNetDict()
        key = meterRead[0].DateTimeToKey(DateTime)
        if(key in meterReadHourlyReadingNetDict):
            return meterReadHourlyReadingNetDict[key]
        else:
            return ['MissingData']

        
    def PopulateMeterUsageDict(self, Meters, DateTime, MeterUsageDict):
        #Dict in the form MeterNumber: [1,2,1,1,1,2,1,1,1,2,4,3,1,1,1]  Where the number is is the # of KWH consumed, and the position in the list is the hour of the day
        import AMR
        for meter in Meters:
            MeterUsageDict[str(meter.meterdcsinumber)] = self.GetMeterUsage(meter, DateTime)

    def PopulateLightUsageDict(self, Lights, DateTime, LightUsageDict):
        #Dict in the form AssemblyID: [1,2,1,1,1,2,1,1,1,2,4,3,1,1,1]  Where the number is is the # of KWH consumed, and the position in the list is the hour of the day
        for light in Lights:
            LightUsageDict[str(light.id)] = []
            for count in range (0,23):  #Eventually add adjustments based on sunrise/sunset time
                if(count in [0,1,2,3,4,5,6,7,19,20,21,22,23,24]):
                    LightUsageDict[str(light.id)].append(light.compatibleunit.wattage / 1000.0)
                else:
                    LightUsageDict[str(light.id)].append(0.0)
              

    def CalculateUsageSummary(self, MeterUsageDict, LightUsageDict):
        UsageList=[]
        for count in range (0, 24):
            UsageList.append(0.0)
        MissingReadingIndicator=False
        TotalMeterUsage = TotalLightUsage = MaxUsage= 0.0
        for key in MeterUsageDict.keys():
            count = 0
            for hourlyreading in MeterUsageDict[key]:
                if(hourlyreading == 'MissingData' or hourlyreading == 'N/A'):
                    MissingReadingIndicator=True
                    break
                UsageList[count]=UsageList[count] + float(hourlyreading)
                TotalMeterUsage=TotalMeterUsage + float(hourlyreading)
                count = count + 1
        for key in LightUsageDict.keys():
            count = 0
            for hourlylightusage in LightUsageDict[key]:
                if(hourlylightusage == 'MissingData' or hourlylightusage == 'N/A'):
                    MissingReadingIndicator=True
                    break
                UsageList[count]=UsageList[count] + float(hourlylightusage)
                TotalLightUsage = TotalLightUsage + float(hourlylightusage)
                count = count + 1
        MaxUsage = max(UsageList)
        return UsageList, MaxUsage, TotalMeterUsage, TotalLightUsage, MissingReadingIndicator

    
        #return TotalUsageList, MaxMeterUsage, MaxLightUsage, TotalMeterUsage, TotalLightUsage
    def PerformAnalysis(self, Transformer, DateTime):
        """Perform an analysis on a transformer.  If there is an existing analysis, overwrite it if successful"""
        Meters=self.GetMetersOffOfTransformer(Transformer)
        Lights=self.GetLightsOffOfTransformer(Transformer)
        MeterUsageDict={}  #Dict in the form MeterNumber: [1,2,1,1,1,2,1,1,1,2,4,3,1,1,1]  Where the number is is the # of KWH consumed, and the position in the list is the hour of the day
        LightUsageDict={}  #Dict in the form AssemblyID: [1,2,1,1,1,2,1,1,1,2,4,3,1,1,1]  Where the number is is the # of KWH consumed, and the position in the list is the hour of the day
        self.PopulateMeterUsageDict(Meters, DateTime, MeterUsageDict)
        self.PopulateLightUsageDict(Lights, DateTime, LightUsageDict)
        TotalUsageList, MaxUsage, TotalMeterUsage, TotalLightUsage, MissingReadingIndicator=self.CalculateUsageSummary(MeterUsageDict, LightUsageDict)

        TransformerUsageAnalysis=djangoproject.models.revenueprotection.TransformerUsageAnalysis()
        TransformerUsageAnalysis.TransformerAssemblyIdentifier=Transformer.id
        TransformerUsageAnalysis.StationIdentifier=Transformer.station.id
        TransformerUsageAnalysis.Point=Transformer.station.the_geom
        TransformerUsageAnalysis.AnalysisDateTime=DateTime
        TransformerUsageAnalysis.CreateDateTime=datetime.datetime.now()
        MeterNumberList=[]
        for meter in Meters:
            MeterNumberList.append(meter.meterdcsinumber)
        TransformerUsageAnalysis.MeterNumberList=str(MeterNumberList)
        TransformerUsageAnalysis.CircuitNumber=Transformer.circuitnumber
        TransformerUsageAnalysis.TotalMeterUsage=TotalMeterUsage
        TransformerUsageAnalysis.TotalLightUsage=TotalLightUsage
        TransformerUsageAnalysis.MaxUsage=MaxUsage
        TransformerUsageAnalysis.MissingReadingIndicator=MissingReadingIndicator
        TransformerCompatibleUnitNameList=[]
        InstallStatus_id_Install=GIS.Coopgisinstallstatus.objects.filter(name='Install')[0].id
        Transformers=GIS.Coopgisassembly.objects.filter(station=Transformer.station).filter(compatibleunit__transformerindicator=True).exclude(installstatus_id=InstallStatus_id_Install)
        transformerBank=GIS.Transformerbank()
        for i in Transformers:
            TransformerCompatibleUnitNameList.append(str(i.id) +':  ' + str(i.compatibleunit.name))
        TransformerUsageAnalysis.TransformerCompatibleUnitNameList=str(TransformerCompatibleUnitNameList)
        TransformersAtStation=GIS.Coopgisassembly.objects.filter(station=Transformer.station).filter(compatibleunit__transformerindicator=True).exclude(installstatus_id=InstallStatus_id_Install)
        if(TransformersAtStation.count > 1):
            TransformerUsageAnalysis.MultipleTransformersExistAtStationIndicator=True
        else:
            TransformerUsageAnalysis.MultipleTransformersExistAtStationIndicator=False
        MeterNumberList=[]
        for meter in Meters:
            MeterNumberList.append(str(meter.meterdcsinumber))
        if(not Meters):
            MeterNumberList=''
        TransformerUsageAnalysis.MeterNumberList=str(MeterNumberList)
        LightCompatibleUnitList=[]
        for light in Lights:
            LightCompatibleUnitList.append(str(light.compatibleunit) + ':  ' + str(light.id))
        else:
            LightCompatibleUnitList=''
        TransformerUsageAnalysis.LightCompatibleUnitList=str(LightCompatibleUnitList)
        TransformerUsageAnalysis.MaxUtilizationPercent=(100 * MaxUsage) / (Transformer.compatibleunit.kva)
        TransformerUsageAnalysis.AverageUtilizationPercent=(100 * (TotalMeterUsage + TotalLightUsage)/24) / (Transformer.compatibleunit.kva) 
        TransformerUsageAnalysis.save()





class COOPIPAdjdetl(models.Model):
    using='default'
    mbrno = models.CharField(max_length=8)
    mbrsep = models.CharField(max_length=11)
    mchdte = models.CharField(max_length=6)
    billmoyr = models.CharField(max_length=4)
    dist = models.CharField(max_length=2)
    transnbr = models.CharField(max_length=4)
    adjdate = models.CharField(max_length=6)
    batch = models.CharField(max_length=4)
    rate = models.CharField(max_length=4)
    class_field = models.CharField(max_length=4, db_column='class') # Field renamed because it was a Python reserved word.
    cycle = models.CharField(max_length=4)
    taxdistcode = models.CharField(max_length=2)
    taxcode = models.CharField(max_length=2)
    meterread = models.CharField(max_length=6)
    kwh = models.IntegerField()
    slkwh = models.IntegerField()
    sltype = models.CharField(max_length=2)
    energy = models.IntegerField()
    fuel = models.IntegerField()
    slchg = models.IntegerField()
    miscchgcode = models.CharField(max_length=2)
    miscchg = models.IntegerField()
    penalty = models.IntegerField()
    weather = models.IntegerField()
    tax = models.IntegerField()
    localtax = models.IntegerField()
    otherchgcode = models.CharField(max_length=2)
    otherchg = models.IntegerField()
    acct = models.CharField(max_length=6)
    racct = models.CharField(max_length=6)
    buditemid = models.CharField(max_length=8)
    voucher = models.CharField(max_length=8)
    conttype = models.CharField(max_length=2)
    contnbr = models.CharField(max_length=8)
    patronage = models.IntegerField()
    xferacct = models.CharField(max_length=11)
    amount = models.IntegerField()
    statecode = models.CharField(max_length=2)
    franchisetax = models.IntegerField()
    comments = models.CharField(max_length=30)
    countycode = models.CharField(max_length=2)
    citycode = models.CharField(max_length=2)
    substation = models.CharField(max_length=4)
    ylrate = models.CharField(max_length=2)
    equipment = models.IntegerField()
    grosstax = models.IntegerField()
    othertax = models.IntegerField()
    roundup = models.IntegerField()
    otheramt1 = models.IntegerField()
    otheramt2 = models.IntegerField()
    otheramt3 = models.IntegerField()
    adjmoyr = models.CharField(max_length=4)
    demandusage = models.IntegerField()
    billeddemand = models.IntegerField()
    measureddmd = models.IntegerField()
    generation = models.IntegerField()
    transmission = models.IntegerField()
    distribution = models.IntegerField()
    environmental = models.IntegerField()
    franchisefee = models.IntegerField()
    todgeneration_001 = models.IntegerField()
    todgeneration_002 = models.IntegerField()
    todtransmision_001 = models.IntegerField()
    todtransmision_002 = models.IntegerField()
    toddistributon_001 = models.IntegerField()
    toddistributon_002 = models.IntegerField()
    todenvirnmentl_001 = models.IntegerField()
    todenvirnmentl_002 = models.IntegerField()
    todfranchisefe_001 = models.IntegerField()
    todfranchisefe_002 = models.IntegerField()
    systemtime = models.CharField(max_length=26)
    sourcecode = models.CharField(max_length=2)
    sourceseq = models.CharField(max_length=8)
    ldevnbr = models.CharField(max_length=4)
    postcode = models.CharField(max_length=2)
    investment = models.IntegerField()
    equiprentalchg = models.IntegerField()
    rental = models.IntegerField()
    leadkvaramt = models.IntegerField()
    lagkvaramt = models.IntegerField()
    cpdemand = models.IntegerField()
    cpdemandamt = models.IntegerField()
    rebateamt = models.IntegerField()
    rebateusage = models.IntegerField()
    pca = models.IntegerField()
    usageamt = models.IntegerField()
    dmdamt = models.IntegerField()
    kva = models.IntegerField()
    kvar = models.IntegerField()
    lokvar = models.IntegerField()
    imaxsoft13_path_01 = models.IntegerField()
    imaxsoft13_path_02 = models.IntegerField()
    imaxsoft13_path_03 = models.IntegerField()
    imaxsoft13_path_04 = models.IntegerField()
    imaxsoft13_path_05 = models.IntegerField()
    imaxsoft13_path_06 = models.IntegerField()
    imaxsoft13_seq_no = models.BigIntegerField()
    COOPIPCreateDateTime=models.DateTimeField(db_column='coopipcreatedatetime')
    MultipleAdjustmentsOnSameMemberSeperatorIndicator=models.NullBooleanField(null=True, blank=True, db_column='multipleadjustmentsonsamememberseperatorindicator')
    CountOfMatchesInCIS=models.IntegerField(db_column='countofmatchesincis')
    CountOfMatchesInCISDateTime=models.DateTimeField(db_column='countofmatchesincisdatetime')
    CountOfMatchesInCISHistoryText=models.TextField(db_column='countofmatchesincishistorytext')
    class Meta:
        db_table = u'coopipadjdetl'
        app_label='djangoproject'
    @staticmethod
    def GetAnyMatchingAdjustment(i, AdjustmentDictionary, CountOnly=False): #i is the Record to Match, AdjustmentDictionary is the Adjustment Dictionary from the other system
        #Key = mbrsep + billmoyr
        Count= 0
        if(not AdjustmentDictionary.has_key(i.mbrsep + i.billmoyr)):
            if(CountOnly):
                return Count
            else:
                return None
        for j in AdjustmentDictionary[i.mbrsep + i.billmoyr]:
            if(j.mbrno == i.mbrno and j.mbrsep == i.mbrsep and j.mchdte == i.mchdte and j.billmoyr == i.billmoyr and 
               j.dist == i.dist and j.transnbr == i.transnbr and j.adjdate == i.adjdate and j.batch == i.batch and 
               j.rate == i.rate and j.class_field == i.class_field and j.cycle == i.cycle and j.taxdistcode == i.taxdistcode and 
               j.taxcode == i.taxcode and j.meterread == i.meterread and j.kwh == i.kwh and j.slkwh == i.slkwh and j.sltype == i.sltype and j.energy == i.energy and 
               j.fuel == i.fuel and j.slchg == i.slchg and 
               j.miscchgcode == i.miscchgcode and j.miscchg == i.miscchg and j.penalty == i.penalty and j.weather == i.weather and j.tax == i.tax and j.localtax == i.localtax and 
               j.otherchgcode == i.otherchgcode and j.otherchg == i.otherchg and j.acct == i.acct and j.racct == i.racct and 
               j.buditemid == i.buditemid and j.voucher == i.voucher and j.conttype == i.conttype and j.contnbr == i.contnbr and 
               j.patronage == i.patronage and j.xferacct == i.xferacct and j.amount == i.amount and j.statecode == i.statecode and 
               j.franchisetax == i.franchisetax and j.comments == i.comments and j.countycode == i.countycode and j.citycode == i.citycode and 
               j.substation == i.substation and j.ylrate == i.ylrate and j.equipment == i.equipment and j.grosstax == i.grosstax and 
               j.othertax == i.othertax and j.roundup == i.roundup and j.otheramt1 == i.otheramt1 and j.otheramt2 == i.otheramt2 and 
               j.otheramt3 == i.otheramt3 and j.adjmoyr == i.adjmoyr and j.demandusage == i.demandusage  and j.billeddemand == i.billeddemand  and 
               j.measureddmd == i.measureddmd and j.generation == i.generation and j.transmission == i.transmission and j.distribution == i.distribution and 
               j.environmental == i.environmental and j.franchisefee == i.franchisefee and j.todgeneration_001 == i.todgeneration_001 and j.todgeneration_002 == i.todgeneration_002 and 
               j.todtransmision_001 == i.todtransmision_001 and j.todtransmision_002 == i.todtransmision_002 and j.toddistributon_001 == i.toddistributon_001 and 
               j.toddistributon_002 == i.toddistributon_002 and j.todenvirnmentl_001 == i.todenvirnmentl_001 and j.todenvirnmentl_002 == i.todenvirnmentl_002 and 
               j.todfranchisefe_001 == i.todfranchisefe_001 and j.todfranchisefe_002 == i.todfranchisefe_002 and 
               j.systemtime == i.systemtime and j.sourcecode == i.sourcecode and j.sourceseq == i.sourceseq and 
               j.ldevnbr == i.ldevnbr and j.postcode == i.postcode and j.investment == i.investment and j.equiprentalchg == i.equiprentalchg and j.rental == i.rental and j.leadkvaramt == i.leadkvaramt and 
               j.lagkvaramt == i.lagkvaramt and j.cpdemand == i.cpdemand and j.cpdemandamt == i.cpdemandamt and j.rebateamt == i.rebateamt and j.rebateusage == i.rebateusage and 
               j.pca == i.pca and j.usageamt == i.usageamt and j.dmdamt == i.dmdamt and j.kva == i.kva and j.kvar == i.kvar and j.lokvar == i.lokvar and 
               j.imaxsoft13_path_01 == i.imaxsoft13_path_01 and j.imaxsoft13_path_02 == i.imaxsoft13_path_02 and j.imaxsoft13_path_03 == i.imaxsoft13_path_03 and 
               j.imaxsoft13_path_04 == i.imaxsoft13_path_04 and j.imaxsoft13_path_05 == i.imaxsoft13_path_05 and 
               j.imaxsoft13_path_06 == i.imaxsoft13_path_06 and j.imaxsoft13_seq_no == i.imaxsoft13_seq_no):
                if(CountOnly):
                    Count = Count + 1
                else:  
                    return j  #return the matching record
        return Count


    @staticmethod
    def AddNewRecord(i,  CISAdjdetlsDict, COOPIPAdjdetlsDict): #i is the CIS Adjustment Record that needs to be added to COOPIP
        New = COOPIPAdjdetl()
        New.mbrno = i.mbrno
        New.mbrsep = i.mbrsep
        New.mchdte = i.mchdte
        New.billmoyr = i.billmoyr
        New.dist = i.dist
        New.transnbr = i.transnbr
        New.adjdate = i.adjdate
        New.batch = i.batch
        New.rate = i.rate
        New.class_field = i.class_field
        New.cycle = i.cycle
        New.taxdistcode = i.taxdistcode
        New.taxcode = i.taxcode
        New.meterread = i.meterread
        New.kwh = i.kwh
        New.slkwh = i.slkwh
        New.sltype = i.sltype
        New.energy = i.energy
        New.fuel = i.fuel
        New.slchg = i.slchg
        New.miscchgcode = i.miscchgcode
        New.miscchg = i.miscchg
        New.penalty = i.penalty
        New.weather = i.weather
        New.tax = i.tax
        New.localtax = i.localtax
        New.otherchgcode = i.otherchgcode
        New.otherchg = i.otherchg
        New.acct = i.acct
        New.racct = i.racct
        New.buditemid = i.buditemid
        New.voucher = i.voucher
        New.conttype = i.conttype
        New.contnbr = i.contnbr
        New.patronage = i.patronage
        New.xferacct = i.xferacct
        New.amount = i.amount
        New.statecode = i.statecode
        New.franchisetax = i.franchisetax
        New.comments = i.comments
        New.countycode = i.countycode
        New.citycode = i.citycode
        New.substation = i.substation
        New.ylrate = i.ylrate
        New.equipment = i.equipment
        New.grosstax = i.grosstax
        New.othertax = i.othertax
        New.roundup = i.roundup
        New.otheramt1 = i.otheramt1
        New.otheramt2 = i.otheramt2
        New.otheramt3 = i.otheramt3
        New.adjmoyr = i.adjmoyr
        New.demandusage = i.demandusage
        New.billeddemand = i.billeddemand
        New.measureddmd = i.measureddmd
        New.generation = i.generation
        New.transmission = i.transmission
        New.distribution = i.distribution
        New.environmental = i.environmental
        New.franchisefee = i.franchisefee
        New.todgeneration_001 = i.todgeneration_001
        New.todgeneration_002 = i.todgeneration_002
        New.todtransmision_001 = i.todtransmision_001
        New.todtransmision_002 = i.todtransmision_002
        New.toddistributon_001 = i.toddistributon_001
        New.toddistributon_002 = i.toddistributon_002
        New.todenvirnmentl_001 = i.todenvirnmentl_001
        New.todenvirnmentl_002 = i.todenvirnmentl_002
        New.todfranchisefe_001 = i.todfranchisefe_001
        New.todfranchisefe_002 = i.todfranchisefe_002
        New.systemtime = i.systemtime
        New.sourcecode = i.sourcecode
        New.sourceseq = i.sourceseq
        New.ldevnbr = i.ldevnbr
        New.postcode = i.postcode
        New.investment = i.investment
        New.equiprentalchg = i.equiprentalchg
        New.rental = i.rental
        New.leadkvaramt = i.leadkvaramt
        New.lagkvaramt = i.lagkvaramt
        New.cpdemand = i.cpdemand
        New.cpdemandamt = i.cpdemandamt
        New.rebateamt = i.rebateamt
        New.rebateusage = i.rebateusage
        New.pca = i.pca
        New.usageamt = i.usageamt
        New.dmdamt = i.dmdamt
        New.kva = i.kva
        New.kvar = i.kvar
        New.lokvar = i.lokvar
        New.imaxsoft13_path_01 = i.imaxsoft13_path_01
        New.imaxsoft13_path_02 = i.imaxsoft13_path_02
        New.imaxsoft13_path_03 = i.imaxsoft13_path_03
        New.imaxsoft13_path_04 = i.imaxsoft13_path_04
        New.imaxsoft13_path_05 = i.imaxsoft13_path_05
        New.imaxsoft13_path_06 = i.imaxsoft13_path_06
        New.imaxsoft13_seq_no = i.imaxsoft13_seq_no
        New.COOPIPCreateDateTime=datetime.datetime.now()
        New.MultipleAdjustmentsOnSameMemberSeperatorIndicator=False
        New.CountOfMatchesInCIS=COOPIPAdjdetl.GetAnyMatchingAdjustment(i, CISAdjdetlsDict, CountOnly=True)
        New.CountOfMatchesInCISDateTime=datetime.datetime.now()
        New.CountOfMatchesInCISHistoryText=""
        New.save()
        if(not COOPIPAdjdetlsDict.has_key(i.mbrsep + i.billmoyr)):        #Key = mbrsep + billmoyr
            COOPIPAdjdetlsDict[i.mbrsep + i.billmoyr] = []
        COOPIPAdjdetlsDict[i.mbrsep + i.billmoyr].append(New)
        print "Added a New Record:  " + str(New.id)

    @staticmethod 
    def PopulateCISAdjdetlsDict():
        CISAdjdetls = list(CIS.Adjdetl.objects.all().order_by('billmoyr'))
        CISAdjdetlsDict = {}  #Key = mbrsep + billmoyr
        for i in CISAdjdetls:
            if(not CISAdjdetlsDict.has_key(i.mbrsep + i.billmoyr)):
                CISAdjdetlsDict[i.mbrsep + i.billmoyr] = []
            CISAdjdetlsDict[i.mbrsep + i.billmoyr].append(i)
        return CISAdjdetlsDict
    @staticmethod
    def PopulateCOOPIPAdjdetlsDict():
        ExistingCOOPIPAdjdetls = list(COOPIPAdjdetl.objects.all().order_by('billmoyr'))
        ExistingCOOPIPAdjdetlsDict = {}
        for i in ExistingCOOPIPAdjdetls:
            if(not ExistingCOOPIPAdjdetlsDict.has_key(i.mbrsep + i.billmoyr)):
                ExistingCOOPIPAdjdetlsDict[i.mbrsep + i.billmoyr] = []
            ExistingCOOPIPAdjdetlsDict[i.mbrsep + i.billmoyr].append(i)
        return ExistingCOOPIPAdjdetlsDict

    @staticmethod
    def UpdateFromCIS():
        CISAdjdetlsDict=COOPIPAdjdetl.PopulateCISAdjdetlsDict()
        COOPIPAdjdetlsDict=COOPIPAdjdetl.PopulateCOOPIPAdjdetlsDict()
        for key in CISAdjdetlsDict.keys():
            for CISAdjustment in CISAdjdetlsDict[key]:  
                COOPIPAdjustment = COOPIPAdjdetl.GetAnyMatchingAdjustment(CISAdjustment, COOPIPAdjdetlsDict, CountOnly=False)
                if(not COOPIPAdjustment):  #Add any new records to COOPIP [which will include records that have any changes in the CIS]
                    COOPIPAdjdetl.AddNewRecord(CISAdjustment, CISAdjdetlsDict, COOPIPAdjdetlsDict)

        COOPIPAdjdetls = list(COOPIPAdjdetl.objects.all().order_by('billmoyr'))
        for i in COOPIPAdjdetls:
            CountOfMatchesInCIS=COOPIPAdjdetl.GetAnyMatchingAdjustment(i, CISAdjdetlsDict, CountOnly=True)
            if not (CountOfMatchesInCIS == i.CountOfMatchesInCIS):
                i.CountOfMatchesInCISHistoryText = i.CountOfMatchesInCISHistoryText + str(New.CountOfMatchesInCISDateTime) + ':  ' + str(CountOfMatchesInCIS) + ',  '
                i.CountOfMatchesInCIS = CountOfMatchesInCIS
                i.CountOfMatchesInCISDateTime = datetime.datetime.now()
                i.save()
            if(COOPIPAdjdetl.objects.filter(mbrno=i.mbrno).filter(billmoyr=i.billmoyr).count() > 1):
                i.MultipleAdjustmentsOnSameMemberSeperatorIndicator = True
                i.save()
            
        


import COOPIP_ReportLabRevenueProtectionReport

